from items import FoodItem
# from message import *
import message
import json
import pymysql


db_conn = None
cursor = None
def connect_db(self):
	try :
		db = 'test'
		host = 'localhost'
		port = 3306
		user =  'root'
		passwd = '1234'

		# 建立数据库连接
		self.db_conn = pymysql.connect(host=host, port=port, db=db, user=user, passwd=passwd)
		# self.db_conn = pymysql.connect(host,user,passwd,db,charset='utf8')
		# 获取游标
		self.cursor = self.db_conn.cursor()
		print("mysql connet successful")
	except:
		print("pymysql connect have something wrong")

def get_data(self):
	sql = "select shopId,allCommentNum from mztr"
	self.cursor.execute(sql)
	results = self.cursor.fetchall()
	dq = 0
	for shopId,allCommentNum in results:
		print("start:"+str(shopId)+"#Overall progress"+str(dq)+"/"+str(len(results)))
		tac = message.parse_four(shopId,allCommentNum)
		print("end:"+str(shopId)+"#Overall progress"+str(dq)+"/"+str(len(results)))
		self.inset_data(tac,shopId)

def inset_data(self,tac,sid):
	try :
		self.cursor.execute("update mztr set commentTags=%s where shopId='"+str(sid)+"'",json.dumps(tac[0],ensure_ascii=False))
		self.cursor.execute("update mztr set comments=%s where shopId='"+str(sid)+"'",json.dumps(tac[1],ensure_ascii=False))
		self.db_conn.commit()
		print("insert successful")
	except:
		print("insert failure")
		self.db_conn.rollback()		# 回滚数据
		print(json.dumps(tac[0],ensure_ascii=False))
		print(type(json.dumps(tac[0],ensure_ascii=False)))
		# print(json.dumps(tac[1],ensure_ascii=False))

# 关闭数据库连接
def close_db(self):
	self.cursor.close()
	self.db_conn.close()

if __name__ =='__main__':
	connect_db()
	get_data()
	close_db()
